package com.eascs.crm.nsEditDetail.dao;

import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.crm.nsEditDetail.entity.CustomerBasicEntity;
import com.eascs.crm.nsEditDetail.vo.CustomerBasicVo;
import com.eascs.crm.nsEditDetail.vo.LoanBaseInfoVO;

@Repository
public class CustomerInfoDao  extends BaseO2oDao{

	public boolean saveEntity(CustomerBasicEntity entity) {
		entity.setCreate_date(new Date());
		entity.setModify_date(new Date());
		return o2oJdbcSession.insert(entity) > 0 ? true : false;
	}
	
	public boolean udpateEntity(CustomerBasicEntity entity) {
		if(StringUtils.isBlank(entity.getLoan_user_id()))
			return false;
		entity.setModify_date(new Date());
		return o2oJdbcSession.updateById(entity) > 0 ? true : false;
	}
	
	public CustomerBasicEntity queryCustomerBasicById(String loanUserId) {
		if(StringUtils.isBlank(loanUserId))
			return null;
		String sql ="SELECT * FROM customer_basic_info WHERE loan_user_id= ?";
		return o2oJdbcSession.findEntity(sql, CustomerBasicEntity.class, loanUserId);
	}
	
	public CustomerBasicVo queryCustomerById(String loanUserId) {
		if(StringUtils.isBlank(loanUserId))
			return null;
		String sql="SELECT c.*,u.user_surname,u.user_name,u.user_full_name,u.user_phone,u.user_phone, u.surname_pinyin AS customerFamilyNamePy, u.name_pinyin AS customerGivenNamePy,u.sex,u.document_type,u.document_no FROM customer_basic_info c,o2o_loan_user u WHERE c.loan_user_id=u.id AND c.loan_user_id= ? ";
		return o2oJdbcSession.findEntity(sql, CustomerBasicVo.class,loanUserId);
	}
	
	/**
	 * @Title: queryCustomerByIdNum
	 * @Description: 根据身份证查询最新的扩展信息
	 * @author 张庆奇
	 * @ param loanUserId
	 * @param idNum
	 * @return
	 * @return CustomerBasicEntity
	 * @throws
	 * @date 2017年8月2日 上午10:53:47
	 */
	public CustomerBasicVo queryCustomerByIdNum(String loanUserId, String idNum) {
		if(StringUtils.isBlank(idNum))
			return null;
		String sql="SELECT c.*,u.loanapply_no FROM customer_basic_info c,o2o_loan_user u WHERE c.loan_user_id=u.id and c.loan_user_id<>? and u.spouse_id is null and u.sponsor_type is null and u.document_no=? ORDER BY create_date desc  limit 1 ";
		return o2oJdbcSession.findEntity(sql, CustomerBasicVo.class, loanUserId,idNum);
	}

	/**
	 * @Title: findCustomerOrMateLoan
	 * @Description: 根据贷款贷款客户id.配偶id，查询客户贷款详细信息
	 * @author LMX
	 * @param vo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @data 2017.8.16
	 */
	public PageResult<Object[]> findCustomerOrMateLoan(LoanBaseInfoVO vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, vo.getPageIndex());

		String sql_str = "";
		StringBuilder sql = new StringBuilder();
		//sql.append("SELECT bill_code, CONCAT( base.crmotoloanersur, base.crmotoloaner ) AS lone_name, concat(UNITCODE, '-', NAMECN) AS ltd_name, pd.ITEMNAME AS refund_bank, base.loan_amount, base.bank_loan_amount, DATE_FORMAT(base.loan_time,'%Y-%m-%d') as loan_time, DATE_FORMAT(base.end_bank_date,'%Y-%m-%d')  as end_bank_date, base.create_time FROM loan_base_info base INNER JOIN hr_units hu ON base.operator_agency = hu.UNITNO left JOIN pub_dditem pd ON base.bankrefund = pd.itemcode AND pd.DDNO = '10340' WHERE 1 = 1 ");
		sql.append("SELECT l.bill_code, pd2.item_name, u.customer_name, l.ltd_name, pd.item_name as name2, l.loana_mount, ld.loan_amount ,");
		sql.append(" ld.loan_date, DATE_ADD( ld.loan_date, INTERVAL ld.loan_term MONTH ), ");
		sql.append(" CASE WHEN l.wf_type='HN' THEN l.settle_date ELSE '' END AS settle_date , l.wf_type, l.create_date");// #当申请状态为‘还款完成：HN’状态时，显示结清日期settle_date，否则为空
		sql.append(" FROM o2o_loanapply l LEFT JOIN o2o_loan_user u ON u.loanapply_no=l.loanapply_no");
		sql.append(" AND l.loan_id = u.loan_id AND u.spouse_id IS NULL AND u.sponsor_type IS NULL");
		sql.append(" LEFT JOIN o2o_bank_loan_data ld ON ld.bill_code=l.bill_code");
		sql.append(" LEFT JOIN pub_dditem pd ON l.bank_loan_name = pd.item_code AND pd.dd_no = '10340'");
		sql.append(" LEFT JOIN pub_dditem pd2 ON l.loan_type = pd2.item_code AND pd2.dd_no = '11321' ");
		sql.append(" WHERE 1=1  ");//增加【主表状态--l.wf_type】;AND l.wf_type != 'T'表示未作废
		// 主表状态（新建：NEW,征信中：Z，征信完成：ZF，内审中：N，内审完成:NF，申报中：S，申报完成：SF,银行驳回：SN,已放款/审批通过：O，作废：T,还款完成：HN，代偿：DC，逾期：YQ）
		// 客户编号
		if (StringUtils.isNotEmpty(vo.getIdNumbers())) {
			String[] idNumbers = vo.getIdNumbers().split(",");
			sql.append(" and u.document_no in (");
			for (int i = 0; i < idNumbers.length; i++) {
				sql.append("'").append(idNumbers[i]).append("'").append(",");
			}
			sql_str = sql.toString();
			sql_str = sql_str.substring(0, sql_str.length() - 1) + ") order by l.payment_time desc";
		}

		sqlPageQuery.setQuerySql(sql_str);
		//PageResult<Object[]> customerBasepage = o2odevDao.findByPage(sqlPageQuery);
		PageResult<Object[]> customerBasepage = o2oJdbcSession.findDataWithPage(sqlPageQuery);
		return customerBasepage;
	
	}
	
	
	/**
	 * @Title: findCustomerOrMateLoan
	 * @Description: 根据贷款贷款客户id.配偶id，查询客户贷款详细信息
	 * @author LMX
	 * @param vo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @data 2017.8.16
	 */
	public PageResult<Object[]> findloanerAndMateLoan(LoanBaseInfoVO vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, vo.getPageIndex());
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT l.bill_code, pd2.item_name, u.customer_name,(select  u.customer_name from o2o_loan_user u where u.loanapply_no=l.loanapply_no and u.spouse_id =l.loan_id) as spouse_name ,");
		sql.append(" l.ltd_name, pd.item_name as name2, l.loana_mount, ld.loan_amount ,");
		sql.append(" ld.loan_date, DATE_ADD( ld.loan_date, INTERVAL ld.loan_term MONTH ), ");
		sql.append(" CASE WHEN l.wf_type='HN' THEN l.settle_date ELSE '' END AS settle_date , l.wf_type, l.create_date");
		sql.append(" FROM (SELECT * from o2o_loanapply y where y.loanapply_no in (SELECT  loanapply_no from o2o_loan_user r where r.document_no='"+vo.getIdNumbers());
		sql.append(" ' or  r.spouse_id in(SELECT  r.loan_id from o2o_loan_user r where r.document_no='"+vo.getIdNumbers()+"')");
		sql.append(" )) l LEFT JOIN o2o_loan_user u ON u.loanapply_no=l.loanapply_no");
		sql.append(" AND l.loan_id = u.loan_id AND u.spouse_id IS NULL AND u.sponsor_type IS NULL");
		sql.append(" LEFT JOIN o2o_bank_loan_data ld ON ld.bill_code=l.bill_code");
		sql.append(" LEFT JOIN pub_dditem pd ON l.bank_loan_name = pd.item_code AND pd.dd_no = '10340'");
		sql.append(" LEFT JOIN pub_dditem pd2 ON l.loan_type = pd2.item_code AND pd2.dd_no = '11321' ");
		sql.append("  ORDER BY  l.payment_time  DESC ");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> customerBasepage = o2oJdbcSession.findDataWithPage(sqlPageQuery);
		return customerBasepage;
	
	}
}
